/***************************************************************************************************
This file create the dataset kappa.dta. 

This dataset includes the coefficient of proportionality necessary to allocate indirect transactions 
done by public businesses from individuals' holdings in public businesses.
A similar approach applied to private businesses prior to 2005.
***************************************************************************************************/


/***************************************************************************************************
Start from national accounts data on holdings and transactions
Convert to US dollars and collapse sectors and asset classes into coarser groups
***************************************************************************************************/
import delimited using "$MyPath/Data/Aggregate Data/nokusd.csv", clear
tempfile temp_nokusd
save `temp_nokusd'

import delimited using "$MyPath/Data/Aggregate Data/cpi.csv", clear
tempfile temp_cpi
save `temp_cpi'

/* Load and clean data */
import delimited using "$MyPath/Data/Aggregate Data/Table10788", varnames(1) rowrange(2) clear
sort year

* Destring some variables 
foreach v in transactions otherchanges {
	replace `v'="." if `v'==".."
	destring `v', replace
}

* Convert from NOK Millions to 2011 Billions USD 
merge m:1 year using `temp_nokusd', nogen keep(master matched)
merge m:1 year using `temp_cpi', nogen keep(master matched)
foreach v in stocks transactions otherchanges {
	replace `v'=`v' / (cpi * nokusd * 1e3)
}
keep if inrange(year, 1995, 2020)
sort year sector item counterpartsector

* Collapse sectors into 8 bigger sectors
rename sector sectori
rename counterpartsector sectorj
foreach b in i j {
	gen sec`b'=.
	replace sec`b'=1 if sector`b' == "S14 Households"  				/* Households */
	replace sec`b'=2 if inlist(sector`b', "S121 Central bank", "S13 General government", "S15 Non-profit institutions serving households")
	replace sec`b'=3 if sector`b' == "S2 Rest of the world" 
	replace sec`b'=4 if sector`b' == "S11 Non financial corporations"
	replace sec`b'=5 if sector`b' == "S122-123 Monetary financial institutions ex Central bank" 
	replace sec`b'=6 if sector`b' == "S124 Non-MM investment funds" 
	replace sec`b'=7 if sector`b' == "S125-127 Other financial institutions"
	replace sec`b'=8 if sector`b' == "S128-129 Insurance corporations and pension funds"
}
assert !missing(seci) & !missing(secj)
label define seclabel ///
1 "Households" ///
2 "Government + NPISH" ///
3 "Foreigners" ///
4 "Nonfinancial corporations" ///
5 "Financial corporations: Monetary financial institutions ex Central bank" ///
6 "Financial corporations: Non-MM investment funds" ///
7 "Financial corporations: Other financial institutions" ///
8 "Financial corporations: Insurance corporations and pension funds" 
label values seci seclabel
label values secj seclabel
collapse (sum) stocks transactions otherchanges, by(year item seci secj)

* Collapse items 14 bigger asset classes
encode item, gen(itemRaw)
** remove pension entitlements
drop if inlist(itemRaw, 13, 14, 31, 32)
gen asset = .
replace asset = 1 if inlist(itemRaw,1, 2, 8, 10, 11, 12, 15, 16, 17, 18) /* other - asset */
replace asset = 2 if itemRaw == 3    /* deposits - asset */
replace asset = 3 if itemRaw == 4	 /* debt securities - asset */
replace asset = 4 if itemRaw == 5	 /* loans - asset */
replace asset = 5 if itemRaw == 6    /* public equity shares - asset */
replace asset = 6 if itemRaw == 7    /* private equity shares - asset */
replace asset = 7 if itemRaw == 9    /* equity fund shares - asset */
replace asset = 8  if inlist(itemRaw, 19, 20, 26, 28, 29, 30, 33, 34, 35, 36)		/* other - liability */
replace asset = 9  if itemRaw == 21  /* deposits - liability */
replace asset = 10 if itemRaw == 22  /* debt securities - liability */
replace asset = 11 if itemRaw == 23  /* loans - liability */
replace asset = 12 if itemRaw == 24  /* public equity shares - liability */
replace asset = 13 if itemRaw == 25  /* private equity shares - liability */
replace asset = 14 if itemRaw == 27  /* equity fund shares - liability */
drop itemRaw item
label define assetlabel ///
1 "A - other" ///
2 "A - deposits" ///
3 "A - debt securities" ///
4 "A - loans" ///
5 "A - public equity shares" ///
6 "A - private equity shares" ///
7 "A - equity fund shares" ///
8 "L - other" ///
9 "L - deposits" ///
10 "L - debt securities" ///
11 "L - loans" ///
12 "L - public equity shares" ///
13 "L - private equity shares" ///
14 "L - equity fund shares"
label values asset assetlabel
collapse (sum) stocks transactions otherchanges, by(year asset seci secj)
keep if inrange(year, 1995, 2020)
save "$MyPath/save/sector", replace

/**************************************************************************************************
Compute consolidated ownership of financial sector
***************************************************************************************************/
use "$MyPath/save/sector", clear
keep if inrange(secj, 4, 8)
keep if inrange(asset, 5, 7)
collapse (sum) stocks, by(year seci secj)
replace stocks = 0 if seci == secj
egen stocks_agg = sum(stocks), by(year secj)
gen s = stocks / stocks_agg
sort year secj seci

* compute and save the matrix Omega every year
preserve
keep if inrange(seci, 4, 8)
foreach year of numlist 1995/2020{
	foreach j of numlist 4/8{
		mkmat s if year == `year' & secj == `j', matrix(s`j')
		matrix list s`j'
	}
	matrix Omega`year' = s4, s5, s6, s7, s8
}
restore

* obtain consolidated ownership shares as (I - Omega') * ss
keep if inrange(seci, 1, 3)
gen s_cons = .
foreach year of numlist 1995/2020{
	foreach i of numlist 1/3{
		mkmat s if year == `year' & seci == `i', matrix(s)
		matrix s_cons = inv(I(5) - Omega`year'') * s
		foreach j of numlist 4/8{
			scalar alpha = s_cons[`j'-3, 1]
			replace s_cons = alpha if seci == `i'  & year == `year' & secj == `j'
		}
	}
}

* check ownership share of each secj sums up to one
egen t = sum(s_cons), by(year secj)
assert abs(t - 1) < 1e-4
drop t

sort year seci secj s s_cons
keep year seci secj s s_cons
save "$MyPath/Save/sector_scons.dta", replace


/**************************************************************************************************
Generate dataset with transactions and holdings by sector. In three parts
Part1: Create net version of each asset by differenciating asset and liabilities
Part 2: Add housing transactions by sector (as absent from financial acccounts)
Part 3: Consolidate using ownership in businesses
***************************************************************************************************/
use "$MyPath/save/sector", clear

* Other
gen double other = stocks if asset == 1
replace other = - stocks if asset == 8

* Deposits
gen double deposits = stocks if asset == 2
replace deposits = - stocks if asset == 9

* Debt (define plus as minus as in micro data)
gen double debt = - stocks if inlist(asset, 3, 4)
replace debt = stocks if inlist(asset, 10, 11)

* Equity (public)
gen double equity_public = stocks if asset == 5
replace equity_public = - stocks if asset == 12

gen double nt_equity_public = transactions if asset == 5
replace nt_equity_public = - transactions if asset == 12

* Equity (private)
gen double equity_pb = stocks if asset == 6
replace equity_pb = - stocks if asset == 13

gen double nt_equity_pb = transactions if asset == 6
replace nt_equity_pb = - transactions if asset == 13

* Equity (fund)
gen double equity_mfund = stocks if asset == 7
replace equity_mfund = - stocks if asset == 14

gen double nt_equity_mfund = transactions if asset == 7
replace nt_equity_mfund = - transactions if asset == 14


* we separate between types of asset: assets issued by household, assets issued by gov, and assets issued by foreign. More precisely, suffix of reach asset refers to the sector issuing this security.
foreach v in deposits debt equity_public nt_equity_public equity_pb nt_equity_pb equity_mfund nt_equity_mfund{
	gen double `v'_household = `v'  if (secj == 1 & asset <= 7) | (seci == 1 & asset > 7)
	gen double `v'_government = `v'  if (secj == 2 & asset <= 7) | (seci == 2 & asset > 7)
	gen double `v'_foreign = `v'   if (secj == 3 & asset <= 7) | (seci == 3 & asset > 7)
	gen double `v'_corporate = `v' if (inrange(secj, 4, 8) & asset <= 7) | (inrange(seci, 4, 8) & asset > 7)
}
collapse (sum) deposits* debt* equity* nt_equity* other, by(year seci)
order year seci equity_public* seci deposits* debt* equity_public* nt_equity_public* equity_pb* nt_equity_pb* nt_equity_mfund*
drop deposits_household *equity*_household *equity*_government

foreach prefix in "nt_" ""{
	foreach suffix in  "" "_foreign"  "_corporate"{
		gen double `prefix'equity`suffix' = `prefix'equity_public`suffix' + `prefix'equity_pb`suffix' + `prefix'equity_mfund`suffix'
	}
}

* check that net assets sum up to zero
foreach v of varlist  equity* nt_equity_* deposits* debt*  {
	egen t = sum(`v'), by(year)
	assert abs(t) < 1e-5
	drop t
}

* Compute proportion of domestic equity directly held by norwegians (Footnote 39)
tempname handle
file open `handle' using "$MyPath/Numbers/ratio_domestic_public.tex", write replace
gen ratio = equity_public_corporate / equity_public
sum ratio if seci == 1
file write `handle' "`: di  %9.0fc 100 * r(mean)'"
file close `handle'

tempfile temp_summary
save `temp_summary'



/* Part 2: Create housing transcation data by sector */
* Start from files with the following encoding for transaction
* Sector 1: households
* Sector 2: missing ID
* Sector 3: public firms
* Sector 4: private firms
* Sector 5: government
import delimited using "$MyPath/Data/Micro Data/housing_transactions_by_sector", clear
rename trans* nt*_housing
sort year
tempfile temp
save `temp'

import delimited using "$MyPath/Data/Micro Data/cooperative_transactions_by_sector", clear
rename trans* nt*_coop
sort year
merge 1:1 year using `temp', nogen

forval jj = 1/5 {
	forval jjj = 1/5 {
		replace nt_`jj'`jjj'_coop = 0 if nt_`jj'`jjj'_coop == .
		replace nt_`jj'`jjj'_coop = nt_`jj'`jjj'_coop/1e9
		replace nt_`jj'`jjj'_housing = nt_`jj'`jjj'_housing/1e9
		gen nt_`jj'`jjj' = nt_`jj'`jjj'_housing + nt_`jj'`jjj'_coop
	}
}

foreach suffix in "" _coop _housing {
	forval jj = 1/5 {
		gen nt_buy_`jj'`suffix' = nt_`jj'1`suffix' + nt_`jj'2`suffix' + nt_`jj'3`suffix' + nt_`jj'4`suffix' + nt_`jj'5`suffix'
		gen nt_sell_`jj'`suffix' = nt_1`jj'`suffix' + nt_2`jj'`suffix' + nt_3`jj'`suffix' + nt_4`jj'`suffix' + nt_5`jj'`suffix'
		
	}
	forval jj = 1/5 {
		gen nt_buy_ext_`jj'`suffix' = nt_buy_`jj'`suffix' - nt_`jj'`jj'`suffix'
		gen nt_sell_ext_`jj'`suffix' = nt_sell_`jj'`suffix' - nt_`jj'`jj'`suffix'
	}
}

gen nt_1 = nt_buy_1-nt_sell_1 /* net purchase by households */
gen nt_2 = nt_buy_2-nt_sell_2 /* net purchase by missing ID */
gen nt_3 = nt_buy_3-nt_sell_3 /* net purchase by public firm */
gen nt_4 = nt_buy_4-nt_sell_4 /* net purchase by private firm */
gen nt_5 = nt_buy_5-nt_sell_5 /* net purchase by government */

sort year
* now convert sectors to the national accounts sectors defined above
gen nt_housing1 = nt_1 
gen nt_housing2 = nt_5
gen nt_housing3 = 0
gen nt_housing4 = nt_2 + nt_3 + nt_4
gen nt_housing5 = 0
gen nt_housing6 = 0
gen nt_housing7 = 0
gen nt_housing8 = 0

keep year nt_housing*
reshape long nt_housing, i(year) j(seci)
sort year seci
keep if year >= 1995
tempfile temp_housing_nt
save `temp_housing_nt'

/* Part 3: Combine everything and consolidate using consolidates ownership shares of each sector */
use  `temp_summary', clear
merge 1:1 seci year using `temp_housing_nt', keep(matched) nogen
*merge 1:1 seci year using `temp_housing_holdings', keep(master matched) nogen
* set zero housing for foreigners and financial corporations
*replace housing = 0 if missing(housing)
replace nt_housing = 0 if missing(nt_housing)

preserve
use "$MyPath/Save/sector_scons", clear
rename seci seci_or
rename secj seci
tempfile temp_ownership
save `temp_ownership'
restore

preserve
merge 1:m year seci using `temp_ownership', keep(matched) nogen
foreach v of varlist deposits* debt* equity* nt_equity*  nt_housing*{
	replace `v' = `v' * s_cons
}
drop seci
rename seci_or seci
collapse (sum) deposits* debt* equity* nt_equity*  nt_housing*, by(year seci)
foreach v of varlist deposits* debt* equity* nt_equity*  nt_housing* {
	rename `v' `v'_ind
}
tempfile temp0
save `temp0'


restore
sort year seci
keep if inrange(seci, 1, 3)
ds deposits* debt* equity* nt_equity*  nt_housing*
local vlist = r(varlist)
merge m:1 seci year using `temp0'
foreach v in `vlist'{
	gen `v'_cons = `v' + `v'_ind
}

* after this, we should have no corporate equity in net supply
assert abs(equity_corporate_cons) < 1e-4 | missing(equity_corporate_cons)
tsset seci year
save "$MyPath/save/sector_consolidated", replace


/***************************************************************************************************
Create kappa that give implicit holdings of each household
kappa_pb -> the ones thorugh private equity
kappa -> the ones thorugh public equity
***************************************************************************************************/

import delimited using "$MyPath/Data/Micro Data/year_cohort", clear
collapse (sum) *_sum, by(year)
foreach v of varlist *_sum{
	replace `v' = `v' / 1e6
}
rename *_sum * 
/* compute implicit holdings private businesses */
gen kappa_pb_debt = debt_pb / stocks_nonreg if year >= 2005
gen kappa_pb_deposits = deposits_pb / stocks_nonreg if year >= 2005
gen kappa_pb_housing = housing_pb / stocks_nonreg if year >= 2005
gen kappa_pb_stocks = stocks_pb / stocks_nonreg if year >= 2005
gen kappa_pb_nt_housing = nt_housing_pb / stocks_nonreg if year >= 2005
gen kappa_pb_nt_stocks = nt_stocks_pb / stocks_nonreg if year >= 2005
gen equityQ = (stocks_nonreg-housing_pb-deposits_pb-stocks_pb+debt_pb) / equity_pb if year >= 2005
foreach v of varlist kappa* equityQ{
	sum `v'
	replace `v' = r(mean) if missing(`v')
}
replace debt_pb = kappa_pb_debt * stocks_nonreg if year < 2005
replace deposits_pb = kappa_pb_deposits * stocks_nonreg if year < 2005
replace housing_pb = kappa_pb_housing * stocks_nonreg if year < 2005
replace stocks_pb = kappa_pb_stocks * stocks_nonreg if year < 2005
replace nt_housing_pb = kappa_pb_nt_housing * stocks_nonreg if year < 2005
replace nt_stocks_pb = kappa_pb_nt_stocks * stocks_nonreg if year < 2005
gen stocks_total = stocks + mfund + stocks_pb
keep year *_pb* stocks_total equityQ
tempfile temp
save `temp'

/* compute implicit holdings public businesses so that total imputed to households equals the total imputed in the sectoral allocation */
use "$MyPath/save/sector_consolidated", clear
keep if seci == 1
merge 1:1 year using `temp', nogen
gen kappa_deposits = (deposits_ind - deposits_pb) / stocks_total
gen kappa_debt = (debt_ind - debt_pb) / stocks_total
gen kappa_nt_stocks = (nt_equity_ind - nt_stocks_pb -  nt_equity_pb * equityQ) / stocks_total
gen kappa_nt_housing = (nt_housing_ind - nt_housing_pb) / stocks_total
sum kappa_deposits kappa_debt kappa_nt_stocks
keep if inrange(year, 1993, 2019)
* National accounts do not have data for 1993/1994, so use average at this year
foreach v of varlist kappa_deposits kappa_debt kappa_nt_stocks kappa_nt_housing{
	sum `v'
	replace `v' = r(mean) if missing(`v')
}
keep year kappa* equityQ
sort year
save "$MyPath/save/kappa", replace


/***************************************************************************************************
Table A3: Indirect holdings through private businesses (share of tax assessed value, 2005–2019 average)
**************************************************************************************************/
use "$MyPath/save/kappa", clear
collapse (mean) kappa* equityQ
tempname handle
file open `handle' using "$MyPath/Tables/TableA3.tex", write replace
file write `handle' "Deposits & $`: di  %3.2fc kappa_pb_deposits[1]'$ & $-$ \\ " _n
file write `handle' "Debt & $`: di  %3.2fc kappa_pb_debt[1]'$ & $-$ \\" _n
file write `handle' "Housing & $`: di  %3.2fc kappa_pb_housing[1]'$ & $`: di  %3.2fc kappa_pb_nt_housing[1]'$ \\" _n
file write `handle' "Stocks & $`: di  %3.2fc kappa_pb_stocks[1]'$ &  $`: di  %3.2fc kappa_pb_nt_stocks[1]'$ \\" _n
file close `handle'

file open `handle' using "$MyPath/Numbers/stat_equityQ.tex", write replace
sum `v'
file write `handle' "`:di %3.2fc equityQ[1]'"
file close `handle'

/************************************************************************************
Figure A2: Aggregated administrative microdata versus the Financial Accounts (Holdings)
*************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/year_cohort", clear
collapse (sum) *_sum, by(year)
foreach v of varlist *_sum{
	replace `v' = `v' / 1e6
}
rename *_sum *
merge 1:1 year using  "$MyPath/save/kappa", keep(master matched) nogen
gen_transactions
tsset year
sort year
tempfile temp
save `temp'

use "$MyPath/save/sector_consolidated", clear
keep if seci == 1
foreach v of varlist *equity* *debt* *deposits* *housing* other {
	cap rename `v' `v'_FA
}
merge 1:1 year using `temp', nogen keep(master matched)
tsset year
keep if inrange(year, 1995, 2019)

gen v_FA = deposits_FA
gen v_micro = deposits
twoway ///
(scatter v_FA year, connect(l) lwidth(medthick) color("black%30")  msymbol(triangle) lpattern(dash)) ///
(scatter v_micro year, connect(l) lwidth(thick) color("black%80")) ///
, ysize(4) xsize(6)  graphregion(color(white) margin(l=0)) ///
xlabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
ytitle("Deposits (B$)", size(medium)) ///
xtitle("") ///
legend(order(1 "Financial accounts" 2 "Micro data") size(medlarge) rows(1) symysize(6) symxsize(8) region(lwidth(vvthin)))
graph export "$MyPath/Figures/FigureA2a.$suffix", replace
drop v_*

gen v_FA = -debt_FA
gen v_micro = -(debt - net_private_debt - bonds)
twoway ///
(scatter v_FA year, connect(l)  lwidth(medthick) color("black%30") msymbol(triangle) lpattern(dash))  ///
(scatter v_micro year, connect(l) lwidth(thick) color("black%80")) ///	  
, ysize(4) xsize(6)  graphregion(color(white) margin(l=0)) ///
xlabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
ytitle("Debt (B$)", size(medium)) ///
xtitle("") ///
legend(order(1 "Financial accounts" 2 "Micro data") size(medlarge) rows(1) symysize(6) symxsize(8) region(lwidth(vvthin)))
graph export "$MyPath/Figures/FigureA2b.$suffix", replace
drop v_*

gen v_FA = equity_public_FA + equity_mfund_FA
gen v_micro = stocks + mfund
twoway 	(scatter v_FA year, connect(l) lwidth(medthick) color("black%30") msymbol(triangle) lpattern(dash)) ///
(scatter v_micro year, connect(l) lwidth(thick) color("black%80")) ///
, ysize(4) xsize(6)  graphregion(color(white) margin(l=0)) ///
xlabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
ytitle("Public Equity (B$)", size(medium)) ///
xtitle("") ///
legend(order(1 "Financial accounts" 2 "Micro data") size(medlarge) rows(1) symysize(6) symxsize(8) region(lwidth(vvthin)))
graph export "$MyPath/Figures/FigureA2c.$suffix", replace
drop v_*

gen v_FA = equity_pb_FA
gen v_micro = stocks_nonreg 
twoway ///
(scatter v_FA year, connect(l) lwidth(medthick) color("black%30") msymbol(triangle) lpattern(dash)) ///	   
(scatter v_micro year, connect(l) lwidth(thick) color("black%80")) /// 
, ysize(4) xsize(6)  graphregion(color(white) margin(l=0)) ///
xlabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
ytitle("Private Equity (B$)", size(medium)) ///
xtitle("") ///
legend(order(1 "Financial accounts" 2 "Micro data") size(medlarge) rows(1) symysize(6) symxsize(8) region(lwidth(vvthin)))
graph export "$MyPath/Figures/FigureA2d.$suffix", replace
drop v_*
